package Dao;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;


import entity.Str;
import entity.User;
import entity.Word;
import util.Dbconnect;

public class WordDaoimpl implements WordDao{

	
	
	
	
	/**
	* Title: searchByname
	* Description:
	* @author: 张文涛
	* @param book
	* @param word
	* @return
	* @throws Exception
	 */
	@Override
	public List<Word> searchByname(String book,String word,Integer limit) throws Exception {
		// TODO Auto-generated method stub
		List<Word> res = new ArrayList<Word>();
		List<String> books = getBooks();
		String sql="select * from "+book+" where word like ?";
		PreparedStatement st = Dbconnect.init(sql);
		st.setString(1,"%"+word+"%");
		List<Word> query = Dbconnect.query(st,Word.class);
		return query;
	}
	

	/**
	* Title: searchByChinese
	* @author: 张文涛
	* @param chinese
	* @return
	* @throws Exception
	 */
	@Override
	public List<Word> searchByChinese(String chinese,Integer limit) throws Exception {
	// TODO Auto-generated method stub
		List<Word> res = new ArrayList<Word>();
		List<String> books = getBooks();
		for(String s: books) {
			String sql="select * from "+s+" where mean_cn like ?";
			PreparedStatement st = Dbconnect.init(sql);
			st.setString(1,"%"+chinese+"%");
			List<Word> query = Dbconnect.query(st,Word.class);
			Dbconnect.close(st);
			for(Word w:query) {
				res.add(w);
				if(limit!=null&&res.size()==limit) {
					return res;
				}
			}
		}
		return res;
	}
	
	/**
	* Title: getWords
	* @author: 张文涛
	* @param book
	* @param begin
	* @param limit
	* @return
	* @throws Exception
	 */
	@Override
	public List<Word> getWords(String book, Integer begin, Integer limit) throws Exception {
		// TODO Auto-generated method stub
		List<Word> res = new ArrayList<Word>();
		List<String> books = getBooks();
		String sql="select * from "+book+" limit ? , ?";
		PreparedStatement st = Dbconnect.init(sql);
		st.setInt(1,begin);
		st.setInt(2,limit);
		List<Word> query = Dbconnect.query(st,Word.class);
		return query;
	}
	
	
	


	/**
	* Title: getBooks
	* @author: 张文涛
	* @return
	* @throws Exception
	 */
	@Override
	public List<String> getBooks() throws Exception {
		// TODO Auto-generated method stub
		List<String> res = new ArrayList<String>();
		String sql="select dbname str from book";
		PreparedStatement st = Dbconnect.init(sql);
		List<Str> query = Dbconnect.query(st,Str.class);
		for(Str s: query) {
			res.add(s.getStr());
		}
		Dbconnect.close(st);
		return res;
	}


	/**
	* Title: searchByname
	* @author: 张文涛
	* @param word
	* @return
	* @throws Exception
	 */
	@Override
	public List<Word> searchByname(String word,Integer limit) throws Exception {
		// TODO Auto-generated method stub
		List<Word> res = new ArrayList<Word>();
		String sql="select DISTINCT * from allword  where word like ? limit ?";
		PreparedStatement st = Dbconnect.init(sql);
		st.setString(1,"%"+word+"%");
		st.setInt(2,limit);
		res = Dbconnect.query(st,Word.class);
		Dbconnect.close(st);
		return res;
	}


	/**
	* Title: searchByChinese
	* @author: 张文涛
	* @param book
	* @param chinese
	* @return
	* @throws Exception
	 */
	@Override
	public List<Word> searchByChinese(String book, String chinese,Integer limit) throws Exception {
		// TODO Auto-generated method stub
		List<Word> res = new ArrayList<Word>();
		String sql="select * from "+book+" where mean_cn like ?";
		if(limit != null) {
			sql = sql + " limit "+limit+" ";
		}
		PreparedStatement st = Dbconnect.init(sql);
		st.setString(1,"%"+chinese+"%");
		res = Dbconnect.query(st,Word.class);
		Dbconnect.close(st);
	    return res;
	}
	
	/**
	 * 
	* Title: updateid
	* @author: 张文涛
	* @throws Exception
	 */
	@Override
	public void updateid() throws Exception {
		Dbconnect.getconnect();
		List<String> books = new WordDaoimpl().getBooks();		
		int cnt =0;
		for(String s: books) {			
			String sql="update "+s+" set id =id+10000";
		    PreparedStatement st = Dbconnect.init(sql);
			Dbconnect.update(st);
			Dbconnect.close(st);
			cnt=0;
			sql="select * from "+s;
			st = Dbconnect.init(sql);
			List<Word> query = Dbconnect.query(st,Word.class);
			Dbconnect.close(st);
			for(Word w:query) {
				cnt++;
				sql="select * from allword where word = ?";
				st = Dbconnect.init(sql);
				st.setString(1,w.getWord());
				Word queryone = Dbconnect.queryone(st,Word.class);
				sql="update "+s+" set id = ? where word = ?";
				st = Dbconnect.init(sql);
				st.setInt(1,queryone.getId());
				st.setString(2,w.getWord());
				Dbconnect.update(st);
				System.out.println(w+"  "+cnt);
			} 
			Dbconnect.close(st);
		}
	}
	


	/**
	* Title: getNonMemaryWordList
	* @author: 张文涛
	* @param book
	* @param email
	* @param limit
	* @return
	* @throws Exception
	 */
	@Override
	public List<Word> getNonMemaryWordList(String book,String email, Integer limit) throws Exception {
		// TODO Auto-generated method stub
		String sql = "SELECT * from "+book+" WHERE id not in (select wordID from record WHERE email = ? )";
		if(limit != null) {
			sql = sql + " limit "+limit+" ";
		}
		PreparedStatement st = Dbconnect.init(sql);
		st.setString(1,email);
		List<Word> words = Dbconnect.query(st,Word.class);
		return words;
	}


	/**
	* Title: getMemaryWordList
	* @author: 张文涛
	* @param book
	* @param email
	* @param limit
	* @return
	* @throws Exception
	 */
	@Override
	public List<Word> getMemaryWordList(String book, String email, Integer limit) throws Exception {
		// TODO Auto-generated method stub
		String sql = "SELECT * from "+book+" WHERE id in (select wordID from record WHERE email = ? )";
		if(limit != null) {
			sql = sql + " limit "+limit+" ";
		}
		PreparedStatement st = Dbconnect.init(sql);
		st.setString(1,email);
		List<Word> words = Dbconnect.query(st,Word.class);
		return words;
	}
	
	
	
	@Override
	public List<Word> getOrderNoMemaryWordList(String book,String email, Integer limit) throws Exception {
		// TODO Auto-generated method stub
		String sql = "SELECT * from "+book+" WHERE id not in (select wordID from record WHERE email = ? ) order by word";
		if(limit != null) {
			sql = sql + " limit "+limit+" ";
		}
		PreparedStatement st = Dbconnect.init(sql);
		st.setString(1,email);
		List<Word> words = Dbconnect.query(st,Word.class);
		return words;
	}


	
	@Override
	public List<Word> getOrderMemaryWordList(String book, String email, Integer limit) throws Exception {
		// TODO Auto-generated method stub
		String sql = "SELECT * from "+book+" WHERE id in (select wordID from record WHERE email = ? ) order by word";
		if(limit != null) {
			sql = sql + " limit "+limit+" ";
		}
		PreparedStatement st = Dbconnect.init(sql);
		st.setString(1,email);
		List<Word> words = Dbconnect.query(st,Word.class);
		return words;
	}


	@Override
	public void addword(String book,Word w) throws Exception {
		// TODO Auto-generated method stub
		String sql = "insert into "+book+" (word,mean_cn,mean_en,sentence,sentence_trans,sentence_phrase,accent,word_etyma) values (?,?,?,?,?,?,?,?)";
		PreparedStatement st = Dbconnect.init(sql);
		st.setString(1,w.getWord());
		st.setString(2,w.getMean_cn());
		st.setString(3,w.getMean_en());
		st.setString(4,w.getSentence());
		st.setString(5,w.getSentence_trans());
		st.setString(6,w.getSentence_phrase());
		st.setString(7,w.getAccent());
		st.setString(8,w.getWord_etyma());
		Dbconnect.update(st);
		
	}


	@Override
	public Word getbyid(Integer id) throws Exception {
		// TODO Auto-generated method stub
		String sql = "SELECT * from allword WHERE id =?";
		PreparedStatement st = Dbconnect.init(sql);
		st.setInt(1,id);
		Word queryone = Dbconnect.queryone(st,Word.class);
		return queryone;
	}
	
	public static void main(String[] args) {
		System.out.println("hello");
	}
	
	

}
